Plugins/Community Based Plugins/Microsoft Sentinel Custom Plugin Scenarios/ASIM Hunting queries/DNSEssentials_HuntingQueries.yaml

Descriptor:
  Name: DNS Essentials Hunting Queries - ASIM 
  DisplayName: DNS Essentials Hunting Queries- ASIM (Preview)
  Description: Network Session normalization schema represents an IP network activity, such as network connections and network sessions. Such events are reported, for example, by operating systems, routers, firewalls, and intrusion prevention systems. 
  Settings:
    - Name: TenantId
      Required: true
    - Name: WorkspaceName
      Required: true
    - Name: SubscriptionId
      Required: true
    - Name: ResourceGroupName
      Required: true
  SupportedAuthTypes:
    - None

SkillGroups:
  - Format: KQL
    Skills:
      - Name: Top 25 DNS queries with most failures in last 24 hours
        DisplayName: Top 25 DNS queries with most failures in last 24 hours (Preview)
        Description: This query searches for DNS queries that resulted in errors. This query utilizes ASIM normalization and is applied to any source that supports the ASIM DNS schema.
        Settings:
          Target: Sentinel
          # The ID of the AAD Organization that the Sentinel workspace is in.
          TenantId: '{{TenantId}}'
          # The id of the Azure Subscription that the Sentinel workspace is in.
          SubscriptionId: '{{SubscriptionId}}'
          # The name of the Resource Group that the Sentinel workspace is in.
          ResourceGroupName: '{{ResourceGroupName}}'
          # The name of the Sentinel workspace.
          WorkspaceName: '{{WorkspaceName}}'
          # This query detects potential network beaconing activity
          Template: |-
            let timeframe = 1d;
            _Im_Dns(starttime=ago(timeframe), endtime=now())
            | where EventSubType =~ 'response' and DnsResponseCodeName != 'NOERROR'
            | summarize Count=count() by DnsQuery, DnsResponseCodeName
            | order by Count
            | take 25
            | extend DNS_0_DomainName = DnsQuery
  - Format: KQL
    Skills:
      - Name: Top 25 Domains with large number of Subdomains
        DisplayName: Top 25 Domains with large number of Subdomains (Preview)
        Description: A large number of subdomains for a domain may be an indicator of a suspicious domain. This query returns the top 25 domains by number of subdomains.
        Settings:
          Target: Sentinel
          # The ID of the AAD Organization that the Sentinel workspace is in.
          TenantId: '{{TenantId}}'
          # The id of the Azure Subscription that the Sentinel workspace is in.
          SubscriptionId: '{{SubscriptionId}}'
          # The name of the Resource Group that the Sentinel workspace is in.
          ResourceGroupName: '{{ResourceGroupName}}'
          # The name of the Sentinel workspace.
          WorkspaceName: '{{WorkspaceName}}'
          # This query detects potential network beaconing activity
          Template: |-
            let lookback=1d;
            _Im_Dns(starttime=ago(lookback),endtime=now())
            | distinct DnsQuery
            | extend DomainParts = split(DnsQuery,'.')
            | extend DomainName = strcat(DomainParts[toint(array_length(DomainParts)-2)],'.',DomainParts[toint(array_length(DomainParts)-1)])
            | summarize SubDomainCount=dcount(DnsQuery),make_list(DnsQuery) by DomainName
            | order by SubDomainCount
            | take 25
            | extend DNS_0_DomainName = DomainName
  - Format: KQL
    Skills:
      - Name: Top 25 Sources(Clients) with high number of errors in last 24 hours
        DisplayName: Top 25 Sources(Clients) with high number of errors in last 24 hours (Preview)
        Description: This query searches for the top 25 clients with the most errors.
        Settings:
          Target: Sentinel
          # The ID of the AAD Organization that the Sentinel workspace is in.
          TenantId: '{{TenantId}}'
          # The id of the Azure Subscription that the Sentinel workspace is in.
          SubscriptionId: '{{SubscriptionId}}'
          # The name of the Resource Group that the Sentinel workspace is in.
          ResourceGroupName: '{{ResourceGroupName}}'
          # The name of the Sentinel workspace.
          WorkspaceName: '{{WorkspaceName}}'
          # This query detects potential network beaconing activity
          Template: |-
            let timeframe = 1d;
            _Im_Dns(starttime=ago(timeframe), endtime=now())
            | where EventSubType == 'response' and DnsResponseCodeName != 'NOERROR'
            | where isnotempty(SrcIpAddr)
            | summarize Count=count() by SrcIpAddr, DnsResponseCodeName
            | order by Count
            | take 25
            | extend IP_0_Address = SrcIpAddr
  - Format: KQL
    Skills:
      - Name: Unexpected top level domains 
        DisplayName: Unexpected top level domains (Preview)
        Description: This query looks for top-level domains that are longer than four characters. 
        Settings:
          Target: Sentinel
          # The ID of the AAD Organization that the Sentinel workspace is in.
          TenantId: '{{TenantId}}'
          # The id of the Azure Subscription that the Sentinel workspace is in.
          SubscriptionId: '{{SubscriptionId}}'
          # The name of the Resource Group that the Sentinel workspace is in.
          ResourceGroupName: '{{ResourceGroupName}}'
          # The name of the Sentinel workspace.
          WorkspaceName: '{{WorkspaceName}}'
          # This query detects potential network beaconing activity
          Template: |-
            // Check in last 24hours
            let looback=1d;
            _Im_Dns(starttime=ago(looback),endtime=now())
            | summarize Count=count() by SrcIpAddr, DnsQuery
            | extend TopLevelDomain = tostring(split(DnsQuery, ".")[-1])
            | where strlen(TopLevelDomain) > 4
            | order by Count
            | take 25
            | extend IP_0_Address = SrcIpAddr
            | extend DNS_0_DomainName = DnsQuery
  - Format: KQL
    Skills:
      - Name: Potential beaconing activity
        DisplayName: Potential beaconing activity (Preview)
        Description: This query identifies beaconing patterns from DNS logs based on recurrent frequency patterns. Such a potential outbound beaconing pattern to untrusted public networks should be investigated for any malware callbacks or data exfiltration attempts. 
        Settings:
          Target: Sentinel
          # The ID of the AAD Organization that the Sentinel workspace is in.
          TenantId: '{{TenantId}}'
          # The id of the Azure Subscription that the Sentinel workspace is in.
          SubscriptionId: '{{SubscriptionId}}'
          # The name of the Resource Group that the Sentinel workspace is in.
          ResourceGroupName: '{{ResourceGroupName}}'
          # The name of the Sentinel workspace.
          WorkspaceName: '{{WorkspaceName}}'
          # This query detects potential network beaconing activity
          Template: |-
            let querystarttime = 2d;
            let queryendtime = 1d;
            let TimeDeltaThreshold = 10;
            let TotalEventsThreshold = 15;
            let PercentBeaconThreshold = 80;
            _Im_Dns(starttime=ago(querystarttime), endtime=ago(queryendtime))
            | where isnotempty(SrcIpAddr)
            | project TimeGenerated, SrcIpAddr, DnsQuery
            | sort by SrcIpAddr asc,TimeGenerated asc
            | serialize
            | extend nextTimeGenerated = next(TimeGenerated, 1), nextSrcIpAddr = next(SrcIpAddr, 1)
            | extend TimeDeltainSeconds = datetime_diff('second',nextTimeGenerated,TimeGenerated)
            | where SrcIpAddr == nextSrcIpAddr
            //Whitelisting criteria/ threshold criteria
            | where TimeDeltainSeconds > TimeDeltaThreshold
            | project TimeGenerated, TimeDeltainSeconds, SrcIpAddr, DnsQuery
            | summarize count(), make_list(TimeDeltainSeconds) by TimeDeltainSeconds, bin(TimeGenerated, 1h), SrcIpAddr, DnsQuery
            | summarize (MostFrequentTimeDeltaCount, MostFrequentTimeDeltainSeconds) = arg_max(count_, TimeDeltainSeconds), TotalEvents=sum(count_)
            by bin(TimeGenerated, 1h), SrcIpAddr, DnsQuery
            | where TotalEvents > TotalEventsThreshold
            | extend BeaconPercent = MostFrequentTimeDeltaCount/toreal(TotalEvents) * 100
            | where BeaconPercent > PercentBeaconThreshold
            | order by BeaconPercent
            | take 50
            | extend IP_0_Address = SrcIpAddr
            | extend DNS_0_DomainName = DnsQuery
  - Format: KQL
    Skills:
      - Name: Possible DNS Tunneling or Data Exfiltration Activity
        DisplayName: Possible DNS Tunneling or Data Exfiltration Activity (Preview)
        Description: Typical domain name lengths are short, whereas domain name queries used for data exfiltration or tunneling can often be very large in size. The hunting query looks for DNS queries that are more than 150 characters long.
        Settings:
          Target: Sentinel
          # The ID of the AAD Organization that the Sentinel workspace is in.
          TenantId: '{{TenantId}}'
          # The id of the Azure Subscription that the Sentinel workspace is in.
          SubscriptionId: '{{SubscriptionId}}'
          # The name of the Resource Group that the Sentinel workspace is in.
          ResourceGroupName: '{{ResourceGroupName}}'
          # The name of the Sentinel workspace.
          WorkspaceName: '{{WorkspaceName}}'
          # This query detects potential network beaconing activity
          Template: |-
            // Setting URI length threshold count, shorter URI's may cause noise, change as needed
            let lookback=1day;
            let uriThreshold = 150;
            let ExcludeDomains=dynamic(["cnr.io", "kr0.io", "arcticwolf.net", "webcfs00.com", "barracudabrts.com", "trendmicro.com", "sophosxl.net", 
            "spotify.com", "e5.sk", "mcafee.com", "opendns.com", "spameatingmonkey.net", "_ldap", "_kerberos", "modsecurity.org", 
            "fdmarc.net", "ipass.com", "wpad"]);
            _Im_Dns(starttime=ago(lookback),endtime=now())
            | summarize count() by SrcIpAddr, DnsQuery
            | where not(DnsQuery has_any (ExcludeDomains))
            | extend Urilength = strlen(DnsQuery)
            | where Urilength >= uriThreshold
            | order by Urilength
            | extend IP_0_Address = SrcIpAddr
            | extend DNS_0_DomainName = DnsQuery
  - Format: KQL
    Skills:
      - Name: Increase in DNS Requests by client than the daily average count
        DisplayName: Increase in DNS Requests by client than the daily average count (Preview)
        Description: Checking for a threefold increase or more in Full Name lookups per client IP for today vs. the daily average for the previous week.
        Settings:
          Target: Sentinel
          # The ID of the AAD Organization that the Sentinel workspace is in.
          TenantId: '{{TenantId}}'
          # The id of the Azure Subscription that the Sentinel workspace is in.
          SubscriptionId: '{{SubscriptionId}}'
          # The name of the Resource Group that the Sentinel workspace is in.
          ResourceGroupName: '{{ResourceGroupName}}'
          # The name of the Sentinel workspace.
          WorkspaceName: '{{WorkspaceName}}'
          # This query detects potential network beaconing activity
          Template: |-
            let starttime = now();
            let endtime = now();
            let lookback = ago(7d);
            //example of excluding Saturday and Sunday in Average as those are potentially low volume and decrease the average, feel free to change
            let excludedDays = dynamic(["Saturday", "Sunday"]);
            // average is across 5 days as we are dropping weekends, change as needed
            let numDays = 5;
            // limit to over 1000 lookups somewhat random but helps focus in on higher lookups, change as needed
            let avglookupThreshold = 3;
            let lookupThreshold = 1000;
            //Setting to startofday so we get 7 days prior to today
            _Im_Dns(starttime=startofday(lookback),endtime=startofday(starttime))
            //getting the associated number of the day of the week so we can map to a given day for later parsing if needed
            | extend DayNumberofWeek = tostring(dayofweek(TimeGenerated))
            //Setting the Day of the week value so that certain days could be excluded if needed
            | extend DayofWeek = iff(DayNumberofWeek == "00:00:00", "Sunday",
            (iff(DayNumberofWeek == "1.00:00:00", "Monday",
            (iff(DayNumberofWeek == "2.00:00:00", "Tuesday",
            (iff(DayNumberofWeek == "3.00:00:00", "Wednesday",
            (iff(DayNumberofWeek == "4.00:00:00", "Thursday",
            (iff(DayNumberofWeek == "5.00:00:00", "Friday",
            (iff(DayNumberofWeek == "6.00:00:00", "Saturday", DayNumberofWeek)))))))))))))
            | where DayofWeek !in~ (excludedDays)
            | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), count() by SrcIpAddr, DnsQuery
            | project StartTime, EndTime, SrcIpAddr, FullNameLookup = DnsQuery, DailyAvgLookupCountOverLastWeek = count_/numDays
            | join ( _Im_Dns
            | where TimeGenerated between(startofday(starttime)..endofday(endtime))
            | summarize count() by SrcIpAddr, FullNameLookup = DnsQuery
            | project SrcIpAddr, LookupCountToday = count_, FullNameLookup
            )
            on SrcIpAddr, FullNameLookup
            | where LookupCountToday > (DailyAvgLookupCountOverLastWeek * avglookupThreshold) and LookupCountToday >= lookupThreshold
            | project StartTime, EndTime, SrcIpAddr, LookupCountToday, DailyAvgLookupCountOverLastWeek, FullNameLookup
            | order by LookupCountToday desc nulls last
            | extend timestamp = StartTime
            | extend IP_0_Address = SrcIpAddr
            | extend DNS_0_DomainName = FullNameLookup
  - Format: KQL
    Skills:
      - Name: Connection to Unpopular Website Detected
        DisplayName: Connection to Unpopular Website Detected (Preview)
        Description: This query lists DNS queries not found in the top 1 million queries in the past 14 days. 
        Settings:
          Target: Sentinel
          # The ID of the AAD Organization that the Sentinel workspace is in.
          TenantId: '{{TenantId}}'
          # The id of the Azure Subscription that the Sentinel workspace is in.
          SubscriptionId: '{{SubscriptionId}}'
          # The name of the Resource Group that the Sentinel workspace is in.
          ResourceGroupName: '{{ResourceGroupName}}'
          # The name of the Sentinel workspace.
          WorkspaceName: '{{WorkspaceName}}'
          # This query detects potential network beaconing activity
          Template: |-
            let min_t = ago(14d);
            let max_t = now();
            let dt = 1d;
            // calculate avg. eps(events per second)
            let eps = materialize (_Im_Dns
              | project TimeGenerated
              | where TimeGenerated > ago(5m)
              | count
              | extend Count = Count / 300);
            let maxSummarizedTime = toscalar (
              union isfuzzy=true 
                  (
                  DNS_Summarized_Logs_ip_CL 
                  | where EventTime_t >= min_t
                  | summarize max_TimeGenerated=max(EventTime_t)
                  | extend max_TimeGenerated = datetime_add('hour', 1, max_TimeGenerated)
                  ),
                  (
                  print(min_t)
                  | project max_TimeGenerated = print_0
                  )
              | summarize maxTimeGenerated = max(max_TimeGenerated) 
              );
            let summarizationexist = materialize(
              union isfuzzy=true 
                  (
                  DNS_Summarized_Logs_ip_CL
                  | where EventTime_t > ago(1d) 
                  | project v = int(2)
                  ),
                  (
                  print int(1) 
                  | project v = print_0
                  )
              | summarize maxv = max(v)
              | extend sumexist = (maxv > 1)
              );
              let allData = ( union isfuzzy=true
                  (
                  (datatable(exists: int, sumexist: bool)[1, false]
                  | where toscalar(eps) > 1000
                  | join (summarizationexist) on sumexist)
                  | join (
                      _Im_Dns(starttime=todatetime(ago(2d)), endtime=ago(dt))
                      | where TimeGenerated > maxSummarizedTime
                      | summarize Count=count() by DnsQuery
                      | top 1000000 by Count
                      | summarize TopOneMillionDNSQuery=make_list(DnsQuery,1000000)
                      | extend exists=int(1)
                      )
                      on exists
                  | project-away exists, maxv, sum*
                  ),
                  (
                  (datatable(exists: int, sumexist: bool)[1, false]
                  | where toscalar(eps) between (501 .. 1000)
                  | join (summarizationexist) on sumexist)
                  | join (
                      _Im_Dns(starttime=todatetime(ago(3d)), endtime=ago(dt))
                      | where TimeGenerated > maxSummarizedTime
                      | summarize Count=count() by DnsQuery
                      | top 1000000 by Count
                      | summarize TopOneMillionDNSQuery=make_list(DnsQuery,1000000)
                      | extend exists=int(1)
                      )
                      on exists
                  | project-away exists, maxv, sum*
                  ),
                  (
                  (datatable(exists: int, sumexist: bool)[1, false]
                  | where toscalar(eps) <= 500
                  | join (summarizationexist) on sumexist)
                  | join (
                      _Im_Dns(starttime=todatetime(ago(4d)), endtime=ago(dt))
                      | where TimeGenerated > maxSummarizedTime
                      | summarize Count=count() by DnsQuery
                      | top 1000000 by Count
                      | summarize TopOneMillionDNSQuery=make_list(DnsQuery,1000000)
                      | extend exists=int(1)
                      )
                      on exists
                  | project-away exists, maxv, sum*
                  ),
                  (
                  DNS_Summarized_Logs_ip_CL
                  | where EventTime_t between (min_t .. ago(dt)) and isnotempty(DnsQuery_s)
                  | project-rename
                      DnsQuery=DnsQuery_s,
                      Count=count__d
                  | extend Count = toint(Count)
                  | summarize TotalCount=toint(sum(Count)) by DnsQuery
                  | top 1000000 by TotalCount
                  | summarize TopOneMillionDNSQuery=make_list(DnsQuery,1000000)
                  )
                  );
            _Im_Dns(starttime=ago(dt),endtime=now())
            | summarize Count=count() by DnsQuery
            | where isnotempty(DnsQuery) and DnsQuery !in (allData)
            | extend DNS_0_DomainName = DnsQuery
  - Format: KQL
    Skills:
      - Name: Anomalous Increase in DNS activity by clients 
        DisplayName: Anomalous Increase in DNS activity by clients  (Preview)
        Description: Checks for an anomalous increase in DNS activity per client in the last 24 hours as compared to the last 14 days. 
        Settings:
          Target: Sentinel
          # The ID of the AAD Organization that the Sentinel workspace is in.
          TenantId: '{{TenantId}}'
          # The id of the Azure Subscription that the Sentinel workspace is in.
          SubscriptionId: '{{SubscriptionId}}'
          # The name of the Resource Group that the Sentinel workspace is in.
          ResourceGroupName: '{{ResourceGroupName}}'
          # The name of the Sentinel workspace.
          WorkspaceName: '{{WorkspaceName}}'
          # This query detects potential network beaconing activity
          Template: |-
            let threshold = 2.5;
            let min_t = ago(14d);
            let max_t = now();
            let dt = 1d;
            // calculate avg. eps(events per second)
            let eps = materialize (_Im_Dns
              | project TimeGenerated
              | where TimeGenerated > ago(5m)
              | count
              | extend Count = Count / 300);
            let maxSummarizedTime = toscalar (
              union isfuzzy=true 
                  (
                  DNS_Summarized_Logs_ip_CL 
                  | where EventTime_t >= min_t
                  | summarize max_TimeGenerated=max(EventTime_t)
                  | extend max_TimeGenerated = datetime_add('hour', 1, max_TimeGenerated)
                  ),
                  (
                  print(min_t)
                  | project max_TimeGenerated = print_0
                  )
              | summarize maxTimeGenerated = max(max_TimeGenerated) 
              );
            let summarizationexist = materialize(
              union isfuzzy=true 
                  (
                  DNS_Summarized_Logs_ip_CL
                  | where EventTime_t > ago(1d) 
                  | project v = int(2)
                  ),
                  (
                  print int(1) 
                  | project v = print_0
                  )
              | summarize maxv = max(v)
              | extend sumexist = (maxv > 1)
              );
            let allData = union isfuzzy=true
                  (
                  (datatable(exists: int, sumexist: bool)[1, false]
                  | where toscalar(eps) > 1000
                  | join (summarizationexist) on sumexist)
                  | join (
                      _Im_Dns(starttime=todatetime(ago(2d)), endtime=now())
                      | where TimeGenerated > maxSummarizedTime and isnotempty(SrcIpAddr)
                      | summarize Count=count() by SrcIpAddr, bin(TimeGenerated, 1h)
                      | extend EventTime = TimeGenerated, Count = toint(Count), exists=int(1)
                      )
                      on exists
                  | project-away exists, maxv, sum*
                  ),
                  (
                  (datatable(exists: int, sumexist: bool)[1, false]
                  | where toscalar(eps) between (501 .. 1000)
                  | join (summarizationexist) on sumexist)
                  | join (
                      _Im_Dns(starttime=todatetime(ago(3d)), endtime=now())
                      | where TimeGenerated > maxSummarizedTime and isnotempty(SrcIpAddr)
                      | summarize Count=count() by SrcIpAddr, bin(TimeGenerated, 1h)
                      | extend EventTime = TimeGenerated, Count = toint(Count), exists=int(1)
                      )
                      on exists
                  | project-away exists, maxv, sum*
                  ),
                  (
                  (datatable(exists: int, sumexist: bool)[1, false]
                  | where toscalar(eps) <= 500
                  | join (summarizationexist) on sumexist)
                  | join (
                      _Im_Dns(starttime=todatetime(ago(4d)), endtime=now())
                      | where TimeGenerated > maxSummarizedTime and isnotempty(SrcIpAddr)
                      | summarize Count=count() by SrcIpAddr, bin(TimeGenerated, 1h)
                      | extend EventTime = TimeGenerated, Count = toint(Count), exists=int(1)
                      )
                      on exists
                  | project-away exists, maxv, sum*
                  ),
                  (
                  DNS_Summarized_Logs_ip_CL
                  | where EventTime_t > min_t and isnotempty(SrcIpAddr_s)
                  | summarize Count=toint(sum(count__d)) by SrcIpAddr=SrcIpAddr_s, bin(EventTime=EventTime_t, 1h)
                  );
            allData
            | make-series TotalEventCountPerDay= sum(Count) on EventTime from min_t to max_t step dt by SrcIpAddr
            | extend (anomalies, score, baseline) = series_decompose_anomalies(TotalEventCountPerDay, threshold, -1, 'linefit')
            | mv-expand anomalies, score, baseline, EventTime, TotalEventCountPerDay
            | extend
              anomalies = toint(anomalies),
              score = toint(score),
              baseline = toint(baseline),
              EventTime = todatetime(EventTime),
              TotalEvents = tolong(TotalEventCountPerDay)
            | where EventTime >= ago(dt)
            | where score >= threshold * 2
            | order by score
            | extend IP_0_Address = SrcIpAddr
  - Format: KQL
    Skills:
      - Name: CVE-2020-1350 (SIGRED) exploitation pattern 
        DisplayName: CVE-2020-1350 (SIGRED) exploitation pattern (Preview)
        Description: This query detects the exploitation pattern of the CVE-2020-1350 (SIGRED) vulnerability. This query utilizes ASIM normalization and is applied to any source that supports the ASIM DNS schema.
        Settings:
          Target: Sentinel
          # The ID of the AAD Organization that the Sentinel workspace is in.
          TenantId: '{{TenantId}}'
          # The id of the Azure Subscription that the Sentinel workspace is in.
          SubscriptionId: '{{SubscriptionId}}'
          # The name of the Resource Group that the Sentinel workspace is in.
          ResourceGroupName: '{{ResourceGroupName}}'
          # The name of the Sentinel workspace.
          WorkspaceName: '{{WorkspaceName}}'
          # This query detects potential network beaconing activity
          Template: |-
            let threshold = 15;
            _Im_Dns(starttime=ago(1d),endtime=now())
            | where DnsQueryTypeName in~ ('SIG', 'RRSIG')
            | where NetworkProtocol =~ 'TCP'
            | summarize Count=count() by SrcIpAddr, bin(TimeGenerated, 1m)
            | where Count > threshold
            | extend IP_0_Address = SrcIpAddr